﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class HoaDon_DAO
    {
        KetnoiDB cn = new KetnoiDB();

        //Thêm dữ liệu
        public static void ThemPlus(HoaDon_DTO ec)
        {

            SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Bakery;Integrated Security=True");
            string query = "INSERT INTO HoaDon VALUES (@ma,@ngay,@tong)";
            if (cnn.State == ConnectionState.Closed)
                cnn.Open();
            SqlCommand cmd = new SqlCommand(query, cnn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@ma", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@ngay", SqlDbType.DateTime);
            cmd.Parameters.Add("@tong", SqlDbType.Int);
            cmd.Parameters["@ma"].Value = ec.MaHD;
            cmd.Parameters["@ngay"].Value = ec.NgayHD;
            cmd.Parameters["@tong"].Value = ec.TongTien;
            //cnn.Open();
            cmd.ExecuteNonQuery();
            //DataTable dt = new DataTable();

            //SqlDataAdapter da = new SqlDataAdapter(cmd);
            //da.Fill(dt);
            //msds.DataSource = dt;
            cnn.Close();
        }
        //Sửa
        public static void SuaPlus(HoaDon_DTO ec)
        {
            SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Bakery;Integrated Security=True");
            string query = "UPDATE HoaDon SET NgayHD =@ngay,TongTien =@tong WHERE MaHD = @ma";
            if (cnn.State == ConnectionState.Closed)
                cnn.Open();
            SqlCommand cmd = new SqlCommand(query, cnn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@ma", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@ngay", SqlDbType.DateTime);
            cmd.Parameters.Add("@tong", SqlDbType.Int);
            cmd.Parameters["@ma"].Value = ec.MaHD;
            cmd.Parameters["@ngay"].Value = ec.NgayHD;
            cmd.Parameters["@tong"].Value = ec.TongTien;
            cmd.ExecuteNonQuery();
            //DataTable dt = new DataTable();

            //SqlDataAdapter da = new SqlDataAdapter(cmd);
            //da.Fill(dt);
            // msds.DataSource = dt;
            cnn.Close();
        }

        //Xóa
        public void XoaDuLieu(HoaDon_DTO et)
        {
            cn.ThucThiCauLenhSQL(@"DELETE FROM HoaDon WHERE MaHD = N'" + et.MaHD + "'");
        }

        //Lấy dữ liệu
        public DataTable LayDuLieu(string DieuKien)
        {
            return cn.GetDataTable("SELECT * FROM HoaDon " + DieuKien);
        }

        //Lấy tổng tiền
        public DataTable LayTongTienHD(string DieuKien)
        {
            return cn.GetDataTable("SELECT SUM(ThanhTien) FROM CTHD " + DieuKien);
        }

    }
}
